﻿#include "StdAfx.h"
#include "DBProc.h"
#include <stdlib.h>

#import <C:\\Program Files\\Common Files\\System\\ado\\msado15.dll>  \
	rename( "EOF", "AdoNSEOF" )
_bstr_t strConnect = 
	"Provider=Microsoft.ACE.OLEDB.12.0;Data Source= Word.accdb;";

char* Add(char* src,int len,char *dic = "EN")
{
	int i;
	int k = 0;
	char *kq = new char[len];
	char*temp = new char[len];
	long t = atol(src);
	t++;
	ltoa(t,temp,10);
	strcpy(kq,dic);
	for(i = 2; i < len-strlen(temp);i++)
	{
		kq[i] = '0';
	}
	for(;i < len;i++)
		kq[i] = temp[k++];
	kq[i] = 0;
	return kq;
}

DBProc::DBProc(void)
{
}


DBProc::~DBProc(void)
{
}

_bstr_t DBProc::GetMeanOfWord(_bstr_t word)
{
	_bstr_t mean;
	LPTSTR type;
	// Initialize COM
	HRESULT hr = CoInitialize(NULL);
	if (FAILED(hr))
	{
		//cout<<": Failed to CoInitialize() COM."<<endl;
		return "Error : Init Com....";
	}
	try
	{
		//Connect to DB
		ADODB::_ConnectionPtr pConn("ADODB.Connection");//Create connection object PConn
		hr = pConn->Open(strConnect,"","",ADODB::adConnectUnspecified);//Connect to the database
		if(SUCCEEDED(hr))//Success->query mean of word
		{
			//Prepare query
			_bstr_t temp = "\"";
			temp += word;
			temp += "\"";
			_bstr_t meanquery = "SELECT CachPhatAm,TuLoai,Nghia,ViDu FROM YNghia,Tu,NghiaTu WHERE Tu.MaTu = NghiaTu.MaTu and NghiaTu.MaYNghia = YNghia.MaYNghia and Tu.CachViet = ";
			meanquery += temp;

			//Execute query and save in recordset object
			ADODB::_RecordsetPtr pRs("ADODB.Recordset");//Create record set to save result of query
			//Execute
			hr = pRs->Open(meanquery, 
				_variant_t((IDispatch*)pConn,true), 
				ADODB::adOpenUnspecified,
				ADODB::adLockUnspecified, 
				ADODB::adCmdText);

			if(SUCCEEDED(hr))//success query
			{
				ADODB::Fields *pFields = NULL;//Init field to get info
				hr = pRs->get_Fields(&pFields);
				int count = pFields->GetCount();
				int n = 0;
				if(SUCCEEDED(hr)&& pFields && pFields->GetCount() > 0)//get success and have result
				{
					while (!pRs->AdoNSEOF)
					{
						for (long nIndex=0; nIndex < pFields->GetCount(); nIndex++)
						{
							if(nIndex == 0 && n != 0)
								continue;
							_variant_t t = pFields->GetItem(nIndex)->GetValue();
							if(t.vt == VT_NULL)
								continue;
							temp = _bstr_t(t);
							if(nIndex == 1)
							{
								if(temp.operator==("n"))
									type = L"Danh từ";
								else if(temp.operator==("v"))
									type = L"Động từ";
								else if(temp.operator==("pp"))
									type = L"Giới từ";
								else if(temp.operator==("aj"))
									type = L"Tính từ";
								else if(temp.operator==("av"))
									type = L"Trạng từ";
								else if(temp.operator==("vt"))
									type = L"Ngoại động từ";
								else if(temp.operator== ("vi"))
									type = L"Nội động từ";
								else if(temp.operator== ("ab"))
									type = L"Viết tắt";
								else
									type = L"Không xác định";
								temp = _bstr_t(type);
							}
							mean += temp;
							mean += "\n";
							temp = "";
						}
						n++;
						pRs->MoveNext();
					}
				}

				pRs->Close();
				pConn->Close();
			}
 		}
	}
	catch(_com_error& e)
	{
		//cout << "_COM_error: "<< e.Description() << endl;
		return "Error....";
	}
	CoUninitialize();
	return mean;
}


bool DBProc::AddWord(_bstr_t pronoun,_bstr_t word,_bstr_t dic,
	_bstr_t mean,_bstr_t type,_bstr_t exam,int flag)
{
	_bstr_t MaTu,MaNghia;
	// Initialize COM
	HRESULT hr = CoInitialize(NULL);
	if (FAILED(hr))
	{
		//cout<<": Failed to CoInitialize() COM."<<endl;
		return false;
	}
	try
	{
		//Connect to DB
		ADODB::_ConnectionPtr pConn("ADODB.Connection");//Create connection object PConn
		hr = pConn->Open(strConnect,"","",ADODB::adConnectUnspecified);//Connect to the database
		_bstr_t newwordcode,newmeancode;
		
		//Prepare query
		//============================================================================================
		//QUERY: get number of word
		ADODB::_RecordsetPtr pRs("ADODB.Recordset");//Create record set to save result of query
		_bstr_t getcount = "SELECT MaTu,MaYNghia FROM NghiaTu WHERE MaTu >= ALL(SELECT MaTu From NghiaTu)";
		pRs->Open(getcount,
			_variant_t((IDispatch*)pConn,true),
			ADODB::adOpenUnspecified,
			ADODB::adLockUnspecified, 
			ADODB::adCmdText
			);
		if(SUCCEEDED(hr))
		{
			ADODB::Fields *pFields = NULL;//Init field to get info
			hr = pRs->get_Fields(&pFields);
			int count = pFields->GetCount();
			int nRow = 0;
			if(SUCCEEDED(hr)&& pFields && count > 0)//get success and have result
			{
				while (!pRs->AdoNSEOF)
				{
					for (long nIndex=0; nIndex < count; nIndex++)
					{
						if(nIndex %2 == 0)
							MaTu = _bstr_t(pFields->GetItem(nIndex)->GetValue());
						else
							MaNghia = _bstr_t(pFields->GetItem(nIndex)->GetValue());
					}
					pRs->MoveNext();
					nRow++;
				}
				if(nRow == 0)//no word found in table
				{
					MaTu = "EN0000000001";
					MaNghia = "EN000000000001";
				}
				else//Create new code of word and mean
				{
					char *code = (char*)MaTu;
					code[0] =code[1] =  '0';
					char *newcode = Add(code,strlen(code));
					newwordcode = _bstr_t(newcode);

					char *m = (char*)MaNghia;
					m[0] = m[1] = '0';
					newcode = Add(m,strlen(m));
					newmeancode = _bstr_t(newcode);
				}
			}
			else
				return false;//Query fail
		}
		else return false;
		pRs->Close();
		//============================================================================================
		//QUERY add word: add row in Tu table
		if(flag != 1)
		{
			_bstr_t addwordquery = "INSERT INTO Tu(MaTu,CachPhatAm,CachViet) VALUES(";
			addwordquery += "\"";
			addwordquery += newwordcode;
			addwordquery += "\",";

			addwordquery += "\"";
			addwordquery += pronoun;
			addwordquery += "\",";

			addwordquery += "\"";
			addwordquery += word;
			addwordquery += "\")";

			hr = pConn->Execute(addwordquery,NULL,ADODB::adExecuteNoRecords);
			if(FAILED(hr))
			{
				//pRs->Close();
				pConn->Close();
				return false;
			}
		}
		//============================================================================================
		//QUERY add mean: add row in YNghia table
		_bstr_t addmeanquery = "INSERT INTO YNghia(MaYNghia,Nghia,ViDu,TuLoai) VALUES(";
		addmeanquery += "\"";
		addmeanquery += newmeancode;
		addmeanquery += "\",";

		addmeanquery += "\"";
		addmeanquery += mean;
		addmeanquery += "\",";

		addmeanquery += "\"";
		addmeanquery += exam;
		addmeanquery += "\",";

		addmeanquery += "\"";
		addmeanquery += type;
		addmeanquery += "\")";

		hr = pConn->Execute(addmeanquery,NULL,ADODB::adExecuteNoRecords);
		if(FAILED(hr))
		{
			//pRs->Close();
			pConn->Close();
			return false;
		}
		//============================================================================================
		//QUERY: add row reference in NghiaTu table
		_bstr_t addref = "INSERT INTO NghiaTu(MaYNghia,MaTu) VALUES(";
		addref += "\"";
		addref += newmeancode;
		addref += "\",";

		addref += "\"";
		if(flag != 1)
			addref += newwordcode;
		else
			addref += MaTu;
		addref += "\")";

		hr = pConn->Execute(addref,NULL,ADODB::adExecuteNoRecords);
		if(FAILED(hr))
		{
			//pRs->Close();
			pConn->Close();
			return false;
		}
		//=============================================================================================
		//QUERY:Update number of word
		if(flag != 1)
		{
			_bstr_t updatecount = "UPDATE TuDien SET SoLuongTu = SoLuongTu + 1";
			hr = pConn->Execute(updatecount,NULL,ADODB::adExecuteNoRecords);
			if(FAILED(hr))
			{
				//pRs->Close();
				pConn->Close();
				return false;
			}
		}
		pConn->Close();
	}
	catch(_com_error* e)
	{
		return false;
	}
	CoUninitialize();
	return true;
}


bool DBProc::DeleteWord(_bstr_t word)
{
	_bstr_t MaTu,MaNghia;
	// Initialize COM
	HRESULT hr = CoInitialize(NULL);
	if (FAILED(hr))
	{
		//cout<<": Failed to CoInitialize() COM."<<endl;
		return false;
	}
	try
	{
		//Connect to DB
		ADODB::_ConnectionPtr pConn("ADODB.Connection");//Create connection object PConn
		hr = pConn->Open(strConnect,"","",ADODB::adConnectUnspecified);//Connect to the database
		//Prepare query
		//=========================================================================================
		//QUERY: Find word code
		_bstr_t findquery = "SELECT Tu.MaTu,NghiaTu.MaYNghia FROM Tu,NghiaTu WHERE Tu.MaTu = NghiaTu.MaTu and Tu.CachViet = ";
		findquery += "\"";
		findquery += word;
		findquery += "\"";

		ADODB::_RecordsetPtr pRs("ADODB.Recordset");//Create record set to save result of query
		//Execute
		hr = pRs->Open(findquery, 
			_variant_t((IDispatch*)pConn,true), 
			ADODB::adOpenUnspecified,
			ADODB::adLockUnspecified, 
			ADODB::adCmdText);
		if(SUCCEEDED(hr))
		{
			ADODB::Fields *pFields = NULL;//Init field to get info
			hr = pRs->get_Fields(&pFields);
			int count = pFields->GetCount();
			int nRow = 0;
			if(SUCCEEDED(hr)&& pFields && pFields->GetCount() > 0)//get success and have result
			{
				while (!pRs->AdoNSEOF)
				{
					for (long nIndex=0; nIndex < pFields->GetCount(); nIndex++)
					{
						if(nIndex %2 == 0)
							MaTu = _bstr_t(pFields->GetItem(nIndex)->GetValue());
						else
							MaNghia = _bstr_t(pFields->GetItem(nIndex)->GetValue());
					}
					pRs->MoveNext();
					nRow++;
				}
				if(nRow == 0)
				{
					pRs->Close();
					pConn->Close();
					return false;//no word found in table
				}
			}
			else
			{
				pRs->Close();
				pConn->Close();
				return false;//Query fail
			}
		}
		else
		{
			pRs->Close();
			pConn->Close();
			return false;
		}
		//=========================================================================================
		//QUERY:Delete mean
		_bstr_t delmeanquery = "DELETE FROM YNghia WHERE MaYNghia = ";
		delmeanquery += "\"";
		delmeanquery += MaNghia;
		delmeanquery +="\"";
		hr = pConn->Execute(delmeanquery,NULL,ADODB::adExecuteNoRecords);

		if(FAILED(hr))
		{	
			pRs->Close();
			pConn->Close();
			return false;
		}
		//==========================================================================================
		//QUERY:Del mean of word
		_bstr_t delref = "DELETE FROM NghiaTu WHERE MaTu = ";
		delref += "\"";
		delref += MaTu;
		delref += "\"";
		hr = pConn->Execute(delref,NULL,ADODB::adExecuteNoRecords);

		if(FAILED(hr))
		{	
			pRs->Close();
			pConn->Close();
			return false;
		}

		//===========================================================================================
		//QUERY :Del word
		_bstr_t delwordquery = "DELETE FROM Tu WHERE MaTu = ";
		delwordquery += "\"";
		delwordquery += MaTu;
		delwordquery +="\"";
		hr = pConn->Execute(delwordquery,NULL,ADODB::adExecuteNoRecords);

		if(FAILED(hr))
		{
			pRs->Close();
			pConn->Close();
			return false;
		}

		//=============================================================================================
		//QUERY:Update number of word
		_bstr_t updatecount = "UPDATE TuDien SET SoLuongTu = SoLuongTu - 1";
		hr = pConn->Execute(updatecount,NULL,ADODB::adExecuteNoRecords);

		if(FAILED(hr))
		{	
			pRs->Close();
			pConn->Close();
			return false;
		}
	}
	catch(_com_error* e)
	{
		return false;
	}
	CoUninitialize();
	return true;
}


_bstr_t DBProc::CountWord(_bstr_t dicname)
{
	_bstr_t SoTu;
	// Initialize COM
	HRESULT hr = CoInitialize(NULL);
	if (FAILED(hr))
	{
		//cout<<": Failed to CoInitialize() COM."<<endl;
		return "Error";
	}
	try
	{
		//Connect to DB
		ADODB::_ConnectionPtr pConn("ADODB.Connection");//Create connection object PConn
		hr = pConn->Open(strConnect,"","",ADODB::adConnectUnspecified);//Connect to the database
		ADODB::_RecordsetPtr pRs("ADODB.Recordset");
		//=========================================================================================
		//QUERY:Get number of dic 
		_bstr_t strsql = "SELECT SoLuongTu FROM TuDien WHERE TuDien.LoaiTuDien = ";
		strsql += "\"";
		strsql += dicname;
		strsql += "\"";

		//Execute
		hr = pRs->Open(strsql, 
			_variant_t((IDispatch*)pConn,true), 
			ADODB::adOpenUnspecified,
			ADODB::adLockUnspecified, 
			ADODB::adCmdText);
		//Get info
		if(SUCCEEDED(hr))
		{
			ADODB::Fields *pFields = NULL;//Init field to get info
			hr = pRs->get_Fields(&pFields);
			int count = pFields->GetCount();
			int nRow = 0;
			if(SUCCEEDED(hr)&& pFields && pFields->GetCount() > 0)//get success and have result
			{
				while (!pRs->AdoNSEOF)
				{
					for (long nIndex=0; nIndex < pFields->GetCount(); nIndex++)
					{
						SoTu = _bstr_t(pFields->GetItem(nIndex)->GetValue());
					}
					pRs->MoveNext();
					nRow++;
				}
				if(nRow == 0)
				{
					pRs->Close();
					pConn->Close();
					return "Error";//no word found in table
				}
			}
			else
			{
				pRs->Close();
				pConn->Close();
				return "Error";//no word found in table
			}
		}
	}
	catch(_com_error* e)
	{
		return "Error";
	}
	CoUninitialize();
	return SoTu;
}


_bstr_t DBProc::ListWord()
{
	_bstr_t list = "";
	// Initialize COM
	HRESULT hr = CoInitialize(NULL);
	if (FAILED(hr))
	{
		//cout<<": Failed to CoInitialize() COM."<<endl;
		return list;
	}
	try
	{
		//Connect to DB
		ADODB::_ConnectionPtr pConn("ADODB.Connection");//Create connection object PConn
		hr = pConn->Open(strConnect,"","",ADODB::adConnectUnspecified);//Connect to the database
		ADODB::_RecordsetPtr pRs("ADODB.Recordset");
		//=========================================================================================
		//QUERY:Get word 
		_bstr_t strsql = "SELECT CachViet From Tu";

		//Execute
		hr = pRs->Open(strsql, 
			_variant_t((IDispatch*)pConn,true), 
			ADODB::adOpenUnspecified,
			ADODB::adLockUnspecified, 
			ADODB::adCmdText);
		//Get info
		if(SUCCEEDED(hr))
		{
			ADODB::Fields *pFields = NULL;//Init field to get info
			hr = pRs->get_Fields(&pFields);
			int count = pFields->GetCount();
			int nRow = 0;
			if(SUCCEEDED(hr)&& pFields && pFields->GetCount() > 0)//get success and have result
			{
				while (!pRs->AdoNSEOF)
				{
					for (long nIndex=0; nIndex < pFields->GetCount(); nIndex++)
					{
						list += _bstr_t(pFields->GetItem(nIndex)->GetValue());
						list += "\n";
					}
					pRs->MoveNext();
					nRow++;
				}
				if(nRow == 0)
				{
					pRs->Close();
					pConn->Close();
					return list;//no word found in table
				}
			}
			else
			{
				pRs->Close();
				pConn->Close();
				return list;//no word found in table
			}
		}
	}
	catch(_com_error* e)
	{
		return list;
	}
	CoUninitialize();
	return list;
}


bool DBProc::AddDic(_bstr_t type,_bstr_t name)
{
	_bstr_t code,temp ;
	// Initialize COM
	HRESULT hr = CoInitialize(NULL);
	if (FAILED(hr))
	{
		//cout<<": Failed to CoInitialize() COM."<<endl;
		return false;
	}
	try
	{
		//Connect to DB
		ADODB::_ConnectionPtr pConn("ADODB.Connection");//Create connection object PConn
		hr = pConn->Open(strConnect,"","",ADODB::adConnectUnspecified);//Connect to the database
		ADODB::_RecordsetPtr pRs("ADODB.Recordset");
		//=========================================================================================
		//Query :test same name//fix lai cho toi uu
		_bstr_t testquery = "SELECT LoaiTuDien,TenTuDien From TuDien";
		pRs->Open(testquery, 
			_variant_t((IDispatch*)pConn,true), 
			ADODB::adOpenUnspecified,
			ADODB::adLockUnspecified, 
			ADODB::adCmdText);
		if(SUCCEEDED(hr))
		{
			_bstr_t dictype,dicname;
			ADODB::Fields *pFields = NULL;//Init field to get info
			hr = pRs->get_Fields(&pFields);
			int count = pFields->GetCount();
			if(SUCCEEDED(hr)&& pFields && pFields->GetCount() > 0)//get success and have result
			{
				while (!pRs->AdoNSEOF)
				{
					for (long nIndex=0; nIndex < pFields->GetCount(); nIndex++)
					{
						if(nIndex % 2 == 0)
							dictype = _bstr_t(pFields->GetItem(nIndex)->GetValue());
						else
							dicname = _bstr_t(pFields->GetItem(nIndex)->GetValue());
					}
					if(dictype == type && dicname == name)
					{
						pRs->Close();
						pConn->Close();
						return false;
					}
					pRs->MoveNext();
				}
			}
			else
			{
				pRs->Close();
				pConn->Close();
				return false;
			}
		}
		//==================================================================================================
		//QUERY:Get dic code
		_bstr_t query = "SELECT MaTuDien From TuDien Where MaTuDien >= ALL(SELECT MaTuDien FROM TuDien)";

		ADODB::_RecordsetPtr pRs1("ADODB.Recordset");
		//Execute
		pRs1->Open(query, 
			_variant_t((IDispatch*)pConn,true), 
			ADODB::adOpenUnspecified,
			ADODB::adLockUnspecified, 
			ADODB::adCmdText);
		if(SUCCEEDED(hr))
		{
			ADODB::Fields *pFields = NULL;//Init field to get info
			hr = pRs1->get_Fields(&pFields);
			int count = pFields->GetCount();
			int nRow = 0;
			if(SUCCEEDED(hr)&& pFields && pFields->GetCount() > 0)//get success and have result
			{
				while (!pRs1->AdoNSEOF)
				{
					for (long nIndex=0; nIndex < pFields->GetCount(); nIndex++)
					{
						temp = _bstr_t(pFields->GetItem(nIndex)->GetValue());
					}
					pRs1->MoveNext();
					nRow++;
				}
				if(nRow == 0)
				{
					pRs1->Close();
					pConn->Close();
					code = "01";//no word found in table
				}
			}
			else
			{
				pRs1->Close();
				pConn->Close();
				return false;
			}
			//=================================================================================================
			//Get new code
			char *i = (char*)temp;
			int dic = atoi(i);
			_itoa(dic+1,i,10);
			code = _bstr_t(i);

			//Add new record to table
			//Query
			_bstr_t addquery = "INSERT INTO TuDien(MaTuDien,LoaiTuDien,TenTuDien,SoLuongTu) VALUES(";
			addquery += "\"0";
			addquery += code;
			addquery += "\",";
			addquery += "\"";
			addquery += type;
			addquery += "\",";
			addquery += "\"";
			addquery += name;
			addquery += "\",";
			addquery += "0)";
			//Execute
			hr = pConn->Execute(addquery,NULL,ADODB::adExecuteNoRecords);
			if(FAILED(hr))
			{
				pConn->Close();
				pRs->Close();
				return false;
			}
		}
		pConn->Close();
		pRs->Close();
		pRs1->Close();
	}
	catch(_com_error* e)
	{
		return false;
	}
	CoUninitialize();
	return true;
}


